package org.springmole;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.springmole.business.bean.DbConnection;

public class TestMetaData {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		
		
		DbConnection dbConnection = new DbConnection("jdbc:postgresql://localhost/migrate", "postgres", "postgres");
		//DbConnection dbConnection = new DbConnection("jdbc:mysql://localhost/srctest_data", "root", "");
		//DbConnection dbConnection = new DbConnection("jdbc:derby:webdba;create=true", "cake", "cake");
		
		try {
			Connection connection = DriverManager.getConnection(dbConnection.getUrl(), dbConnection.getUserName(), dbConnection.getPassword());
			DatabaseMetaData databaseMetaData = connection.getMetaData();
			System.out.println(databaseMetaData.getDriverVersion());
			System.out.println(databaseMetaData.getSchemaTerm());
			
			String parentCatalog=null, parentSchema=null, parentTable="advertisementplacement", foreignCatalog=null, foreignSchema=null, foreignTable=null;
			
			ResultSet crossReferenceResultSet = databaseMetaData.getCrossReference(parentCatalog, parentSchema, parentTable, foreignCatalog, foreignSchema, foreignTable);
			while(crossReferenceResultSet.next()) {
				System.out.println("---------");
				System.out.println(crossReferenceResultSet.getString("PKTABLE_NAME") + " " + crossReferenceResultSet.getString("PKCOLUMN_NAME"));
				System.out.println(crossReferenceResultSet.getString("FKTABLE_NAME") + " " + crossReferenceResultSet.getString("FKCOLUMN_NAME"));
			}
			
			
			
			// no catalog in Derby
			// DataBase name in Postgres and MySQL
			System.out.println("------catalog-----------");
			ResultSet resultSetCatalog = databaseMetaData.getCatalogs();
			while(resultSetCatalog.next()) {
				System.out.println(resultSetCatalog.getString("TABLE_CAT"));
			}
			
			// no schema in MySQL
			// "Public" is the default one in Postres
			// "APP" is the default one in Derby
			System.out.println("------schema-----------");
			ResultSet resultSetSchema = databaseMetaData.getSchemas();
			while(resultSetSchema.next()) {
				//System.out.println(resultSetSchema.getString("TABLE_CATALOG")); 
				System.out.println(resultSetSchema.getString("TABLE_SCHEM"));
			}			
			
			System.out.println("------tabletype-----------");
			ResultSet resultSetTableType = databaseMetaData.getTableTypes();
			while (resultSetTableType.next()) {
				System.out.println(resultSetTableType.getString("TABLE_TYPE"));
				
			}			
			
			System.out.println("------table-----------");
			//String[] tableTypeArray = {"TABLE"};
			String[] tableTypeArray = null;
			
			ResultSet resultSetTable = databaseMetaData.getTables(null, null, null, tableTypeArray);
			while (resultSetTable.next()) {
				System.out.println(resultSetTable.getString("TABLE_NAME"));
				
			}
			
			System.out.println("------product-----------");
			Statement statement = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE, 
					ResultSet.CONCUR_READ_ONLY, 
					ResultSet.CLOSE_CURSORS_AT_COMMIT);
			
			ResultSet resultSet = statement.executeQuery("SELECT * FROM product;");
			ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
			int columnCount = resultSetMetaData.getColumnCount();
			for(int c=1; c<=columnCount; c++) {
				System.out.println("--------- name " + resultSetMetaData.getColumnName(c));
				System.out.println("label " + resultSetMetaData.getColumnLabel(c));
				System.out.println("type " + resultSetMetaData.getColumnType(c));
				System.out.println("typeName " + resultSetMetaData.getColumnTypeName(c));
				System.out.println("tableName " + resultSetMetaData.getTableName(c));
				System.out.println("precision " + resultSetMetaData.getPrecision(c));
				System.out.println("scale " + resultSetMetaData.getScale(c));
			}
			
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
